package lu.med.praxiswahl.wahlumed.dao.beans;

import java.util.List;

import javax.ejb.Local;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import lu.med.praxiswahl.wahlumed.dao.beans.daointerface.InternAnalysisDAOInt;
import lu.med.praxiswahl.wahlumed.model.beans.CustomerTBL;
import lu.med.praxiswahl.wahlumed.model.beans.InternAnalysisTBL;

/**
 * Session Bean implementation class InternAnalysisDAO
 * 
 * @author Patrick kendzo Email: pkendzo@msn.com
 */

@Stateless
@Local(
{ InternAnalysisDAOInt.class })
public class InternAnalysisDAO implements InternAnalysisDAOInt
{

    @PersistenceContext(unitName = "gynaekomedic-ejbPU")
    private EntityManager em = null;

    /**
     * Default constructor.
     */
    public InternAnalysisDAO()
    {

    }

    @Override
    public void delete(Long id)
    {
	InternAnalysisTBL analysis = em.find(InternAnalysisTBL.class, id);

	CustomerTBL cust = analysis.getCustomer();

	cust.getAnalysis().remove(analysis);

	em.remove(analysis);
	em.flush();

    }

    @Override
    public InternAnalysisTBL retrieveAnalysisByID(Long id)
    {
	return em.find(InternAnalysisTBL.class, id);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> retrieveAnalysisByYEAR(String year)
    {
	try
	{
	    return (List<InternAnalysisTBL>) em.createQuery(
		    "select a from InternAnalysisTBL a where a.datum like %"
			    + year).getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("Could not retrieve Analysis by year", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> retrieveAnalysisByYEAR_And_Month(
	    String year, String month)
    {
	try
	{
	    return (List<InternAnalysisTBL>) em.createQuery(
		    "select a from InternAnalysisTBL a where a.datum like "
			    + year + "% and like %-" + month + "-%")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException(
		    "Could not retrieve Analysis by year and month", e);
	}
    }

    @Override
    public void persist(InternAnalysisTBL todo)
    {
	em.persist(todo);
	em.flush();

    }

    @Override
    public void update(InternAnalysisTBL todo)
    {
	em.merge(todo);
	em.flush();

	// Object[] tmp =
	// getAnaInternItemSortedByNumber(todo.getYear()).toArray();
	// ZytologyTBL[] eintraege = new ZytologyTBL[tmp.length];
	/*
	 * for (int i = 0; i < tmp.length; i++) { ((InternAnalysisTBL)
	 * tmp[i]).setInternAnaNumber(i + 1); }
	 */
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> retrieveAnalysisByCustomer(String matricule)
    {
	try
	{
	    return (List<InternAnalysisTBL>) em.createQuery(
		    "select a from InternAnalysisTBL a where a.customer = '"
			    + matricule + "'").getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException(
		    "Could not retrieve Analysis for this Customer", e);
	}
    }

    @Override
    public int getActualInterAnaNumberIndexByYEAR(String year)
    {
	try
	{

	    Integer result = (Integer) em.createQuery(
		    "select max(a.internAnaNumber) from InternAnalysisTBL a where a.year ='"
			    + year + "'").getSingleResult();

	    return result.intValue();
	}
	catch (Exception e)
	{
	    // throw new RuntimeException("OrdonanceTBL is empty",
	    // e);
	    return 0;
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> getInternAnaItemsOfINTERVAL(
	    String fromDatum, String toDatum)
    {
	try
	{
	    return (List<InternAnalysisTBL>) em
		    .createQuery(
			    "SELECT a FROM InternAnalysisTBL a where  STR_TO_DATE(a.datum,'%d.%m.%Y') between "
				    + "STR_TO_DATE('"
				    + fromDatum
				    + "','%d.%m.%Y') and  STR_TO_DATE('"
				    + toDatum
				    + "','%d.%m.%Y') order by a.year, a.internAnaNumber")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException(
		    "Could not retrieve Analysis for this Interval", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> getLast_50_ItemOfInterAnaByYEAR(String year)
    {
	try
	{
	    return (List<InternAnalysisTBL>) em
		    .createQuery(
			    "SELECT a FROM InternAnalysisTBL a where  a.EINTRAG_NUMMER in"
				    + " SELECT a FROM (SELECT a.EINTRAG_NUMMER FROM InternAnalysisTBL a order by a.EINTRAG_NUMMER asc)"
				    + "where rownum <= 50 AND a.datum='" + year
				    + "' order by a.internAnaNumber")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("Could not retrieve Analysis for year "
		    + year, e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> getCOMBUR9_IF_ALL_ENTRY_EMPTY()
    {
	try
	{
	    return (List<InternAnalysisTBL>) em
		    .createQuery(
			    "SELECT a FROM InternAnalysisTBL a where  (a.leukocyten is null OR a.leukocyten = \'\')"
				    + " AND (a.nitrit is null OR a.nitrit = \'\')"
				    + " AND (a.pH is null OR a.pH = \'\')"
				    + " AND (a.protein is null OR a.protein = \'\')"
				    + " AND (a.glucose is null OR a.glucose = \'\')"
				    + " AND (a.keton is null OR a.keton = \'\')"
				    + " AND (a.uroBilonogene is null OR a.uroBilonogene = \'\')"
				    + " AND (a.bilirubin is null OR a.bilirubin = \'\')"
				    + " AND (a.blut is null OR a.blut = \'\')"
				    + " AND (a.haemoglobin is null OR a.haemoglobin = \'\') order by a.internAnaNumber")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("Could not retrieve Analysis ", e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> get_OB_POS()
    {
	try
	{
	    return (List<InternAnalysisTBL>) em
		    .createQuery(
			    "SELECT a FROM InternAnalysisTBL a where a.ob_pos = true order by a.year, a.internAnaNumber asc")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException("Could not retrieve a OB_POS Analysis ",
		    e);
	}
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<InternAnalysisTBL> getAnaInternItemSortedByNumber(String year)
    {
	try
	{
	    return (List<InternAnalysisTBL>) em.createQuery(
		    "SELECT i FROM InternAnalysisTBL i where year = '" + year
			    + "' order by STR_TO_DATE(i.datum,'%d.%m.%Y') ")
		    .getResultList();
	}
	catch (Exception e)
	{
	    throw new RuntimeException(
		    "Could not sorted Intern analysis items for this year", e);
	}

    }
}
